﻿using System;
using System.Data;
using System.Collections.Generic;
using System.IO;
using Oracle.DataAccess.Client;
using Wicresoft.SelfService.DAO;
using System.Linq;
using System.Configuration;
using System.Web;
using System.Text;

namespace Wicresoft.SelfService.DAL
{
    public class OSDAL : IDisposable
    {
        private OracleDataAccess oda;
        private OracleTransaction oTran;
        public OSDAL()
        {
            oda = new OracleDataAccess();
            oda.Open();
        }

        private OS TranDataToOSModel(OracleDataReader dr)
        {

            OS os = new OS();
            os.ID = Convert.ToInt32(dr["ID"]);
            os.OSName = dr["OSNAME"].ToString();
            return os;
        }

        public OS GetOSByID(int id)
        {
            OS os = new OS();
            string sql = "select * from os_table where id='" + id + "'";
            using (var reader = oda.ExecuteReader(sql))
            {
                while (reader.Read())
                {
                    if (reader["ID"] != DBNull.Value) os.ID = Convert.ToInt32(reader["ID"]);
                    if (reader["OSName"] != DBNull.Value) os.OSName = (string)reader["OSName"];
                }
            }
            return os;
        }

        public List<OS> GetAllOS()
        {
            List<OS> os = new List<OS>();
            string sql = "select * from os_table order by osname";
            using (OracleDataReader dr = oda.ExecuteReader(sql))
            {
                while (dr.Read())
                {
                    OS o1 = TranDataToOSModel(dr);
                    os.Add(o1);
                }
            }
            return os;
        }

        public bool Create(OS model)
        {
            string sql = string.Format("insert into os_table(osname)values('{0}')", model.OSName);
            int count = oda.ExecuteCommand(sql);
            if (count > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public bool Delete(decimal id)
        {
            string sql = string.Format("delete from os_table where id={0}", id);
            int count = oda.ExecuteCommand(sql);
            if (count > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public bool Update(OS model)
        {
            string sql = string.Format("update os_table set osname='{0}' where id={1}", model.OSName, model.ID);
            int count = oda.ExecuteCommand(sql);
            if (count > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public bool IsExist(string osName)
        {
            var oslist = GetAllOS();
            bool reslut = false;
            string n = null;

            if (oslist.Count > 0)
            {
                foreach (var item in oslist)
                {
                    n = item.OSName.ToLower();
                    if (n == osName.ToLower())
                    {
                        reslut = true;
                        break;
                    }
                }
            }

            return reslut;
        }

        public void commitTransaction()
        {
            if (oTran != null) oTran.Commit();
        }
        public void rollbackTransaction()
        {
            if (oTran != null) oTran.Rollback();
        }
        public void closeDBConnection()
        {
            if (oda != null && oda.conn != null) oda.Close();
        }
        public void Dispose()
        {
            closeDBConnection();
        }

    }
}
